//*********************************************************
//
//    Copyright (c) Microsoft. All rights reserved.
//    This code is licensed under the Apache License, Version 2.0.
//    THIS CODE IS PROVIDED *AS IS* WITHOUT WARRANTY OF
//    ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING ANY
//    IMPLIED WARRANTIES OF FITNESS FOR A PARTICULAR
//    PURPOSE, MERCHANTABILITY, OR NON-INFRINGEMENT.
//
//********************************************************* 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Research.eResearch;

namespace Microsoft.TridentWordAddIn.Common
{
    public class SQLConnectionHelper
    {
        /// <summary>
        /// Gets the database list.
        /// </summary>
        /// <param name="isTrusted">if set to <c>true</c> [is trusted].</param>
        /// <param name="serverName">Name of the server.</param>
        /// <param name="userName">Name of the user.</param>
        /// <param name="password">The password.</param>
        /// <returns></returns>
        public static string[] GetDatabaseList(bool isTrusted, string serverName, string userName, string password)
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            if (isTrusted)
            {
                builder.IntegratedSecurity = true;
                builder.DataSource = serverName;
                builder.InitialCatalog = "master";
            }
            else
            {
                builder.IntegratedSecurity = false;
                builder.UserID = userName;
                builder.Password = password;
                builder.DataSource = serverName;
                builder.InitialCatalog = "master";
            }

            List<string> list = new List<string>();
            string[] collection = new string[] { "master", "model", "msdb", "tempdb" };
            List<string> list2 = new List<string>(collection);
            SqlConnection con = new SqlConnection(builder.ConnectionString);
            con.Open();
            SqlCommand command = new SqlCommand("sp_databases", con);
            command.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    string item = reader.GetString(reader.GetOrdinal("DATABASE_NAME"));
                    if (!(list.Contains(item) || list2.Contains(item)))
                    {
                        list.Add(item);
                    }
                }
            }
            catch (SqlException)
            {
                return null;
            }
            return list.ToArray();
        }


        /// <summary>
        /// Tests the connection.
        /// </summary>
        /// <param name="isTrusted">if set to <c>true</c> [is trusted].</param>
        /// <param name="connectionName">Name of the connection.</param>
        /// <param name="serverName">Name of the server.</param>
        /// <param name="dbName">Name of the db.</param>
        /// <param name="userName">Name of the user.</param>
        /// <param name="password">The password.</param>
        /// <returns></returns>
        public static bool TestConnection(bool isTrusted, string connectionName, string serverName, string dbName, string userName, string password)
        {
            SQLConnectionParameters.Helper helper = new SQLConnectionParameters.Helper();
            BackendStorageException exp;
            IConnMgrInfoParams param;
            Connection conn;

            if (isTrusted)
            {
                param = SQLConnectionParameters.CreateTrustedConnection(
                                                    connectionName,
                                                    serverName,
                                                    dbName);
            }
            else
            {
                param = SQLConnectionParameters.CreateUserPassConnection(
                                                    connectionName,
                                                    userName,
                                                    password,
                                                    serverName,
                                                    dbName);
            }

            try
            {
                conn = helper.OpenConnection(param);

            }
            catch (ConnectionFailure)
            {
                return false;
            }
            return (conn.Test(out exp));
        }
    }
}
